from pymysql import Connection
from datetime import datetime

from test import log_type


def connectDb():
    '''
    用于连接数据库
    :return: 数据库链接
    '''
    try:
        conn = Connection(
            host='localhost',
            port=3306,
            user='root',
            password='123456',
            database='db03',
            charset='utf8',
            autocommit=True
        )
        return conn
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return None


def closeDb(conn):
    if conn:
        conn.close()


def findAll(conn):
    '''
    用查找全部的日志记录
    :param conn: 数据库链接
    :return: 日志
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select * from log_rec
            '''
            cursor.execute(sql)
            # 返回结果
            dataAll = cursor.fetchall()
            for data in dataAll:
                # 改日期格式
                formatted_time = data[1].strftime('%Y-%m-%d %H:%M:%S')
                data = list(data)
                data[1] = formatted_time
                print(data)
        except Exception as e:
            print(f"查询失败: {e}")
            return None


def addRec(conn, data):
    '''
    添加日志记录
    :param conn: 数据库连接
    :param data: 添加的数据
    :return: 执行情况
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                insert into log_rec values (null,%s,%s,%s)
            '''
            cursor.execute(sql, data)
            print(f"添加成功: {data}")
        except Exception as e:
            print(f"添加失败: {e}")
            return None

def findById(conn, id):
    '''
    根据id查询日志记录及类型
    :param conn: 数据库连接
    :param id: 日志id
    :return: 查询结果
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select 
                    r.id,r.occ_time,t.t_id,r.log_det,t.type_des,t.type_lev
                from 
                    log_rec r
                join 
                    log_type t
                on 
                    r.log_type_id=t.t_id
            '''
            cursor.execute(sql)
            dataAll = cursor.fetchall()
            for data in dataAll:
                # 改日期格式
                formatted_time = data[1].strftime('%Y-%m-%d %H:%M:%S')
                data = list(data)
                data[1] = formatted_time
                return  data
        except Exception as e:
            print(f"查询失败: {e}")
            return None

def delRecById(conn, id):
    '''
    根据id删除日志记录
    :param conn: 数据库连接
    :param id: 日志id
    :return: 执行情况
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                delete from log_rec where id=%s
            '''
            cursor.execute(sql, id)
            result=findById(conn, id)
            print(f"删除成功: {result}")
        except Exception as e:
            print(f"删除失败: {e}")
            return None


def findByTime(conn, startTime, endTime):
    '''
    查询该时间段内的所有日志记录及类型
    :param conn: 数据库连接
    :param startTime: 起始时间
    :param endTime:终止时间
    :return:查询结果
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select 
                    r.id,r.occ_time,t.t_id,r.log_det,t.type_des,t.type_lev
                from 
                    log_rec r
                join 
                    log_type t
                on
                    r.log_type_id=t.t_id 
                and 
                    r.occ_time between %s and %s
            '''
            cursor.execute(sql, (startTime, endTime))
            dataAll = cursor.fetchall()
            for data in dataAll:
                # 改日期格式
                formatted_time = data[1].strftime('%Y-%m-%d %H:%M:%S')
                data = list(data)
                data[1] = formatted_time
                print(data)
        except Exception as e:
            print(f"查询失败: {e}")
            return None


def findByTypeLev(conn, lev):
    '''
    根据日志类型等级查询日志记录及类型
    :param conn: 数据库连接
    :param lev: 日志类型等级
    :return: 查询结果
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select 
                    r.id,r.occ_time,t.t_id,r.log_det,t.type_des,t.type_lev
                from 
                    log_type t
                join 
                    log_rec r
                on
                    r.log_type_id=t.t_id 
                where 
                    t.type_lev=%s
            '''
            cursor.execute(sql, lev)
            dataAll = cursor.fetchall()
            # print(dataAll)
            for data in dataAll:
                # 改日期格式
                formatted_time = data[1].strftime('%Y-%m-%d %H:%M:%S')
                data = list(data)
                data[1] = formatted_time
                print(data)
        except Exception as e:
            print(f"查询失败: {e}")
            return None


def findDesTop3(conn):
    '''
    查询找出 Top 3 最频繁出现的错误消息
    :param conn: 数据库连接
    :return: 查询结果
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select 
                    r.log_type_id,count(*) cnt
                from 
                    log_rec r
                group by log_type_id
                order by cnt desc
                limit 3
            '''
            cursor.execute(sql)
            dataAll = cursor.fetchall()
            for data in dataAll:
                print(data)
        except Exception as e:
            print(f"查询失败: {e}")
            return None


def countLev(conn):
    '''
    按级别统计日志数量
    :param conn:数据连接
    :return:类型描述[类型] 条数
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select t.type_des,t.t_id,count(r.log_type_id) c
                from log_rec r 
                join log_type t 
                on r.log_type_id=t.t_id
                group by r.log_type_id,t.type_lev
                order by 
                    case t.type_lev 
                        when 'CRITICAL' then 1  
                        when 'ERROR' then 2
                        when 'WARN' then 3
                        when 'INFO' then 4
                        when 'DEBUG' then 5    
                        else 6                  
                    end;
            '''
            cursor.execute(sql)
            dataAll = cursor.fetchall()
            for data in dataAll:
                data = list(data)
                print(f'{data[0]}[{data[1]}]  {data[2]}条')
        except Exception as e:
            print(f"查询失败: {e}")
            return None


def findAllType(conn):
    '''
    查询全部的日志类型
    :param conn: 数据连接
    :return: 日志id及类型描述
    '''
    if conn:
        try:
            cursor = conn.cursor()
            sql = '''
                select t_id,type_des from log_type
            '''
            cursor.execute(sql)
            dataAll = cursor.fetchall()
            i = 1
            for data in dataAll:
                print(f'{i}.{data}')
                i += 1
        except Exception as e:
            print(f"查询失败: {e}")
            return None


if __name__ == '__main__':
    conn = connectDb()
    # findAll(conn)
    # 添加时要选择日志类型
    # addRec(conn,('2025-05-01 10:10:10','INFO','用户登录成功: user=admin'))
    # delRecById(conn,21)
    # findByTime(conn,'2025-03-10 11:10:10','2025-05-01 10:10:10')
    # findByTypeLev(conn,'高')
    # findDesTop3(conn)
    # countLev(conn)
    while True:
        print('欢迎来到日志管理系统:')
        print('1. 查询所有日志记录及类型')
        print('2. 添加日志记录')
        print('3. 根据id删除日志记录')
        print('4. 按时间段查询日志记录及类型')
        print('5. 根据日志类型等级查询日志记录及类型')
        print('6. 查询找出 Top 3 最频繁出现的错误消息')
        print('7. 按类别统计日志数量')
        print('8. 退出')
        chooise = input('请选择: ')
        if chooise == '1':
            findAll(conn)
        elif chooise == '2':
            rec = []
            utime = input('请输入日志发生的时间(格式如:xxxx-xx-xx xx:xx:xx): ')
            rec.append(utime)
            print('日志类型: ')
            findAllType(conn)
            utype = int(input('请选择日志类型: '))
            while True:
                if utype > 5 or utype < 1:
                    print('输入错误,请重新输入:')
                    continue
                if utype == 1:
                    rec.append('CRITICAL')
                    break
                elif utype == 2:
                    rec.append('DEBUG')
                    break
                elif utype == 3:
                    rec.append('ERROR')
                    break
                elif utype == 4:
                    rec.append('INFO')
                    break
                elif utype == 5:
                    rec.append('WARN')
                    break
            udet = input('请输入日志详情: ')
            rec.append(udet)
            addRec(conn, rec)
        elif chooise == '3':
            id = input('请输入要删除记录的id: ')
            delRecById(conn, id)
        elif chooise == '4':
            startTime = input('请输入你要查询日志的起始时间: ')
            endTime = input('请输入你要查询日志的终止时间: ')
            findByTime(conn, startTime, endTime)
        elif chooise == '5':
            lev = input('请输入你要查询日志的类型等级: ')
            findByTypeLev(conn, lev)
        elif chooise == '6':
            findDesTop3(conn)
        elif chooise == '7':
            countLev(conn)
        elif chooise == '8':
            closeDb(conn)
            exit()
        else:
            print('输入错误,请重新输入')
